package com.kuhh.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.kuhh.dao.IGradeDao;
import com.kuhh.pojo.Grade;
import com.kuhh.utils.JdbcUtils;

public class GradeDaoImpl implements IGradeDao{
	private Connection conn;
	private PreparedStatement ps;
	private ResultSet rs;

	@Override
	public List<Grade> getGradeList() {
		// TODO Auto-generated method stub
		List<Grade> list = new ArrayList<>();
		try {
			conn = JdbcUtils.getConnection();
			String sql = "select * from tb_grade order by create_time desc";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			
			while(rs.next()) {
				Grade grade = new Grade();
				grade.setGradeNo(rs.getString(1));
				grade.setGradeName(rs.getString(2));
				grade.setTeacherNo(rs.getString(3));
				grade.setCreateTime(rs.getDate(4));
				grade.setUpdateTime(rs.getDate(5));
				list.add(grade);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcUtils.close(conn, ps, rs);
		}
		return list;
	}

	@Override
	public Grade getGradeByNo(String gradeNo) {
		// TODO Auto-generated method stub
		try {
			conn = JdbcUtils.getConnection();
			String sql = "select * from tb_grade where grade_no = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, gradeNo);
			rs = ps.executeQuery();
			while(rs.next()) {
				Grade grade = new Grade();
				grade.setGradeNo(gradeNo);
				grade.setGradeName(rs.getString(2));
				grade.setTeacherNo(rs.getString(3));
				grade.setCreateTime(rs.getDate(4));
				grade.setUpdateTime(rs.getDate(5));
				return grade;
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcUtils.close(conn, ps, rs);
		}
		return null;
	}

	@Override
	public boolean addGrade(Grade grade) {
		// TODO Auto-generated method stub
		try {
			conn = JdbcUtils.getConnection();
			String sql = "insert into tb_grade values(?,?,?,?,?)";
			ps = conn.prepareStatement(sql);
			ps.setString(1, grade.getGradeNo());
			ps.setString(2, grade.getGradeName());
			ps.setString(3, grade.getTeacherNo());
			ps.setDate(4, new Date(grade.getCreateTime().getTime()));
			ps.setDate(5, new Date(grade.getUpdateTime().getTime()));
			ps.executeUpdate();
			return true;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcUtils.close(conn, ps, rs);
		}
		return false;
	}

	@Override
	public boolean updateGrade(Grade grade) {
		// TODO Auto-generated method stub
		try {
			conn = JdbcUtils.getConnection();
			String sql = "update tb_grade set grade_name = ?, teacher_no = ?, update_time = ? where grade_no";
			ps = conn.prepareStatement(sql);
			ps.setString(1, grade.getGradeName());
			ps.setString(2, grade.getTeacherNo());
			ps.setDate(3, new Date(grade.getUpdateTime().getTime()));
			ps.setString(4, grade.getGradeNo());
			ps.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcUtils.close(conn, ps, rs);
		}
		return false;
	}

	@Override
	public boolean deleteGradeByNo(String gradeNo) {
		// TODO Auto-generated method stub
		try {
			conn = JdbcUtils.getConnection();
			String sql = "delete from tb_grade where grade_no = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, gradeNo);
			ps.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcUtils.close(conn, ps, rs);
		}
		return false;
	}

}
